package com.motiv.contracts;

import android.provider.BaseColumns;

/**
 * Created by jdr on 23/08/13.
 */
public final class GoalContract {

    public static final String SQL_CREATE_GOAL_TABLE =
            "CREATE TABLE " + GoalEntry.TABLE + " (" +
                    GoalEntry._ID + " INTEGER PRIMARY KEY," +
                    GoalEntry.COLUMN_NAME + " TEXT"  +
            " )";
    public static final String SQL_CREATE_FAMILY_TABLE =
             "CREATE TABLE " + FamilyEntry.TABLE + " (" +
                    FamilyEntry._ID + " INTEGER PRIMARY KEY," +
                    FamilyEntry.COLUMN_PARENT + " INTEGER," +
                    FamilyEntry.COLUMN_CHILD + "  INTEGER"  +
            " )";

    public static final String SQL_CREATE_VIEW_PARENTS =
            "CREATE VIEW " + Views.PARENTS + " AS " +
                    " SELECT " + " * FROM " + GoalEntry.TABLE +
                    " LEFT JOIN " + FamilyEntry.TABLE + " ON " +
                    GoalEntry.TABLE + "." + GoalEntry._ID +
                    " = " + FamilyEntry.COLUMN_PARENT;

    public static final String SQL_CREATE_VIEW_CHILDREN =
            "CREATE VIEW " + Views.CHILDREN + " AS " +
                    " SELECT " + " * FROM " + GoalEntry.TABLE +
                    " LEFT JOIN " + FamilyEntry.TABLE + " ON " +
                    GoalEntry.TABLE + "." + GoalEntry._ID +
                    " = " + FamilyEntry.COLUMN_CHILD;

    public static final String SQL_DELETE_GOAL_TABLE =
            "DROP TABLE IF EXISTS " + GoalEntry.TABLE;

    public static final String SQL_DELETE_FAMILY_TABLE =
            "DROP TABLE IF EXISTS " + FamilyEntry.TABLE;

    public static final String SQL_DELETE_PARENTS_VIEW =
            "DROP VIEW IF EXISTS " + Views.PARENTS;

    public static final String SQL_DELETE_CHILDREN_VIEW =
            "DROP VIEW IF EXISTS " + Views.CHILDREN;

    //empty constructor to stop others from instantiating contracts class
    public GoalContract() {}

    /* Inner class that defines table contents */
    public static abstract class GoalEntry implements BaseColumns {
        public static final String TABLE = "goals";
        public static final String COLUMN_NAME = "name";
    }

    /* Inner class that defines table contents */
    public static abstract class FamilyEntry implements BaseColumns {
        public static final String TABLE = "family";
        public static final String COLUMN_PARENT = "parent";
        public static final String COLUMN_CHILD = "child";
    }

    /* Inner class that defines views contents */
    public static abstract class Views {
        public static final String PARENTS = "parents";
        public static final String CHILDREN = "children";
    }
}
